#include "cDatabase.h"
#include "macros.h"

#define CLEAN_DATABASE 0

#if DEBUG
long long cDatabase::initmagic;
#endif
cDatabase* cDatabase::instance;


// SQLITE
#if 0
int sqlite3_prepare_v2(
  sqlite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  sqlite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
//SQLITE_STATIC SQLITE_TRANSIENT
#endif


// STAIC GETTER FUNCTIONS

cDatabase* cDatabase::Get() {
#if DEBUG
	if( initmagic != 0x123456789ABCDEF0 ) ERRORLOG("cDatabase::Get(): Not initialized.");
#endif
	return instance;
}


bool cDatabase::Init(const char* path) {
#if DEBUG
	if( initmagic == 0x123456789ABCDEF0 ) ERRORLOG("cDatabase::Init(): Already initialized!");
#endif
	instance = new cDatabase(path);
#if DEBUG & CLEAN_DATABASE
#define CREATE_TABLE_STMT(x) "DROP TABLE IF EXISTS " #x); cDatabase::Get()->Query( "CREATE TABLE " #x " "
#else
#define CREATE_TABLE_STMT(x) "CREATE TABLE IF NOT EXISTS " #x " "
#endif

	cDatabase::Get()->Query(
		CREATE_TABLE_STMT(`main`.`users`) "("
		"`id` INTEGER  PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE,"
		"`username` VARCHAR NOT NULL,"
		"`passhash` VARCHAR NOT NULL,"
		"`name` VARCHAR NOT NULL,"
		"`accessgroup` INTEGER NOT NULL,"
		"`teacherid` INTEGER NOT NULL,"
		"`chataccess` INTEGER NOT NULL)");
	cDatabase::Get()->Query(
		CREATE_TABLE_STMT(`main`.`loginhistory`) "("
		"`id` INTEGER  PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE,"
		"`userid` INTEGER NOT NULL,"
		"`logindate` INTEGER NOT NULL,"
		"`duration` INTEGER NOT NULL)");
	cDatabase::Get()->Query(
		CREATE_TABLE_STMT(`main`.`testresults`) "("
		"`id` INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL UNIQUE,"
		"`userid` INTEGER NOT NULL,"
		"`testid` INTEGER NOT NULL,"
		"`datetaken` INTEGER NOT NULL,"
		"`score` INTEGER NOT NULL)");
	cDatabase::Get()->Query(
		CREATE_TABLE_STMT(`main`.`tests`) "("
		"`id` INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL UNIQUE,"
		"`title` VARCHAR NOT NULL,"
		"`dateadded` INTEGER NOT NULL)");
	cDatabase::Get()->Query(
		CREATE_TABLE_STMT(`main`.`homework`) "("
		"`id` INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL UNIQUE,"
		"`userid` INTEGER NOT NULL,"
		"`testid` INTEGER NOT NULL,"
		"`dateset` INTEGER NOT NULL,"
		"`datedue` INTEGER NOT NULL,"
		"`resultid` INTEGER)");
	cDatabase::Get()->Query(
		CREATE_TABLE_STMT(`main`.`testquestions`) "("
		"`id` INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL UNIQUE,"
		"`testid` INTEGER NOT NULL,"
		"`question` VARCHAR NOT NULL,"
		"`questiontype` INTEGER NOT NULL,"
		"`answer` VARCHAR NOT NULL)");
	cDatabase::Get()->Query(
		CREATE_TABLE_STMT(`main`.`homework`) "("
		"`id` INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL UNIQUE,"
		"`userid` INTEGER NOT NULL,"
		"`testid` INTEGER NOT NULL,"
		"`dateset` INTEGER NOT NULL,"
		"`datedue` INTEGER NOT NULL,"
		"`resultid` INTEGER NOT NULL)");

#if DEBUG & CLEAN_DATABASE
	cDatabase::Get()->Query("INSERT INTO `main`.`users` (username, passhash, name, teacherid, accessgroup) VALUES "
		"('sysuser', 'nologin', '[n/a]', -1, 10)");
	cDatabase::Get()->Query("INSERT INTO `main`.`users` (username, passhash, name, teacherid, accessgroup) VALUES "
		"('admin', 'a3c88fc21faf602e324e0c02aa87fb5d8b505bd70d972db49c77988c0abf1d85', 'System Administrator', -1, 2)");
	cDatabase::Get()->Query("INSERT INTO \"main\".\"users\" (username, passhash, name, teacherid, accessgroup) VALUES "
		"('sebi',  '9f669f9ec79b330955a4ff90f98885b33d80bffc2424fd684a04f835c8acee7b', 'Sebastian Grygiel', 4, 1)");
	cDatabase::Get()->Query("INSERT INTO \"main\".\"users\" (username, passhash, name, teacherid, accessgroup) VALUES "
		"('mxi',   '5c305f0181de288900ec124b90836d3a42779ac6259fcf282d95b71d62b3f1c0', 'Motoko Ishikawa \xe3\x80\x8a\xe7\x9f\xb3\xe5\xb7\x9d\xe7\xb4\xa0\xe5\xad\x90\xe3\x80\x8b', -1, 2)");
	cDatabase::Get()->Query("INSERT INTO \"main\".\"users\" (username, passhash, name, teacherid, accessgroup) VALUES "
		"('kikisan',  'f3343647925dcfd74d8844921fb26b5e1325823ee6ec97e70cee898b34a317ab', 'Kiki Tanzila', 4, 0)");
	cDatabase::Get()->Query("INSERT INTO \"main\".\"users\" (username, passhash, name, teacherid, accessgroup) VALUES "
		"('ehsan',  '221068457a3efcae94f3e68c201952503e52c703eb457c39865d41590e6fac5f', 'Ehsan Hussain', 7, 0)");
	cDatabase::Get()->Query("INSERT INTO \"main\".\"users\" (username, passhash, name, teacherid, accessgroup) VALUES "
		"('teacher2',  '752a908fa8a433275cf38451c98957ba7b38aea46883d34d8a5f3a9c744ff466', 'Test Teacher 2', -1, 2)");
#endif

	DBResult res = instance->Query("SELECT 1 FROM users WHERE username = 'admin'");
	if(res.GetRows() == 0)
		cDatabase::Get()->Query("INSERT INTO `main`.`users` (username, passhash, name, teacherid, accessgroup, chataccess) VALUES "
			"('admin', 'a3c88fc21faf602e324e0c02aa87fb5d8b505bd70d972db49c77988c0abf1d85', 'System Administrator', -1, 2, 1)");

	return instance->ready;
}

// STATIC CODE TO STRING

std::string cDatabase::ErrorCodeToString(int code) {
	// this macro used as: codecase(SQLITE_OK) will translate to:  case SQLITE_OK: return "SQLITE_OK";
#define codecase(x) case x: return #x;
	switch (code)
	{
		codecase(SQLITE_OK        )
		codecase(SQLITE_ERROR     )
		codecase(SQLITE_INTERNAL  )
		codecase(SQLITE_PERM      )
		codecase(SQLITE_ABORT     )
		codecase(SQLITE_BUSY      )
		codecase(SQLITE_LOCKED    )
		codecase(SQLITE_NOMEM     )
		codecase(SQLITE_READONLY  )
		codecase(SQLITE_INTERRUPT )
		codecase(SQLITE_IOERR     )
		codecase(SQLITE_CORRUPT   )
		codecase(SQLITE_NOTFOUND  )
		codecase(SQLITE_FULL      )
		codecase(SQLITE_CANTOPEN  )
		codecase(SQLITE_PROTOCOL  )
		codecase(SQLITE_EMPTY     )
		codecase(SQLITE_SCHEMA    )
		codecase(SQLITE_TOOBIG    )
		codecase(SQLITE_CONSTRAINT)
		codecase(SQLITE_MISMATCH  )
		codecase(SQLITE_MISUSE    )
		codecase(SQLITE_NOLFS     )
		codecase(SQLITE_AUTH      )
		codecase(SQLITE_FORMAT    )
		codecase(SQLITE_RANGE     )
		codecase(SQLITE_NOTADB    )
		codecase(SQLITE_ROW       )
		codecase(SQLITE_DONE      )
	default:
		return "Unknown";
	}
#undef codecase
};

// CONSTRUCTOR

cDatabase::cDatabase(const char* path) {
#if DEBUG
	if( initmagic == 0x123456789ABCDEF0 ) ERRORLOG("cDatabase::Init(): Already initialized!");
	initmagic = 0x123456789ABCDEF0;
#endif
	ready = Open(path);
	
}


// Init functions
bool cDatabase::Open(const char* filename) {
	return sqlite3_open(filename, &dbhandle) == SQLITE_OK;   
}

void cDatabase::Close() {
#if DEBUG
	if( initmagic != 0x123456789ABCDEF0 ) ERRORLOG("cDatabase::Close(): Not initialized!");
	initmagic = 0;
#endif
	sqlite3_close(dbhandle);
}


// Query
DBResult cDatabase::Query(const char* query) {
	sqlite3_stmt *statement;
	std::vector< std::vector<std::string> > results;
	std::vector< std::vector<bool> > nulls;

	if(sqlite3_prepare_v2(dbhandle, query, -1, &statement, 0) == SQLITE_OK) {
		int cols = sqlite3_column_count(statement);
		int result = 0;
		while(true) {
			result = sqlite3_step(statement);
			
			if(result == SQLITE_ROW) {
				std::vector<std::string> values;
				std::vector<bool> nulls_row;
				for(int col = 0; col < cols; col++) {
					char* val = (char*)sqlite3_column_text(statement, col);
					nulls_row.push_back( val == nullptr );
					values.push_back( val == nullptr ? "(null)" : val );
				}
				nulls.push_back(nulls_row);
				results.push_back(values);
			}else{
				break;   
			}
		}
		sqlite3_finalize(statement);
	}else{
		const char* err = sqlite3_errmsg(dbhandle);
		ERRORLOG("cDatabase::Query(): SQLite STATEMENT PREPARE error: %s\n", err);
		return DBResult(results, nulls); // an empty set
	}
	std::string err = sqlite3_errmsg(dbhandle);
	if(err != "not an error") ERRORLOG("cDatabase::Query(): SQLite Error: %s\n", err.c_str());
	
	return DBResult(results, nulls);  
}

cDBStatement* cDatabase::PrepareQuery(const char* query) {
	sqlite3_stmt *statement;
	if(sqlite3_prepare_v2(dbhandle, query, -1, &statement, 0) == SQLITE_OK) {
		return new cDBStatement(statement);
	}else{
		const char* err = sqlite3_errmsg(dbhandle);
		ERRORLOG("cDatabase::PrepareQuery(): SQLite STATEMENT PREPARE error: %s\n", err);
		return nullptr;
	}
}

void cDBStatement::bindInt( int idx, int val ) {
	if(stmt == nullptr) return;
	if(sqlite3_bind_int( stmt, idx, val ) != SQLITE_OK)
		ERRORLOG("cDatabase::bindInt(): SQLite BIND error: %s\n", sqlite3_errmsg(cDatabase::Get()->dbhandle));
}

void cDBStatement::bindString( int idx, std::string str ) {
	if(stmt == nullptr) return;
	if(sqlite3_bind_text( stmt, idx, str.c_str(), -1, SQLITE_TRANSIENT) != SQLITE_OK)
		ERRORLOG("cDatabase::bindInt(): SQLite BIND error: %s\n", sqlite3_errmsg(cDatabase::Get()->dbhandle));
}

void cDBStatement::bindBlob( int idx, unsigned char* val, int len ) {
	if(stmt == nullptr) return;
	if(sqlite3_bind_blob( stmt, idx, val, len, SQLITE_TRANSIENT) != SQLITE_OK)
		ERRORLOG("cDatabase::bindInt(): SQLite BIND error: %s\n", sqlite3_errmsg(cDatabase::Get()->dbhandle));
}

DBResult cDBStatement::execute() {
	std::vector< std::vector<std::string> > results;
	std::vector< std::vector<bool> > nulls;
	if(stmt == nullptr) return DBResult(results, nulls);

	int cols = sqlite3_column_count(stmt);
	int result = 0;
	while(true) {
		result = sqlite3_step(stmt);
			
		if(result == SQLITE_ROW) {
			std::vector<std::string> values;
			std::vector<bool> nulls_row;
			for(int col = 0; col < cols; col++) {
				char* val = (char*)sqlite3_column_text(stmt, col);
				nulls_row.push_back( val == nullptr );
				values.push_back( val == nullptr ? "(null)" : val );
			}
			nulls.push_back(nulls_row);
			results.push_back(values);
		}else{
			break;   
		}
	}
	sqlite3_finalize(stmt);

	std::string err = sqlite3_errmsg(cDatabase::Get()->dbhandle);
	if(err != "not an error") ERRORLOG("cDatabase::Query(): SQLite Error: %s\n", err.c_str());
	
	return DBResult(results, nulls);  
}